package com.topscomm.mpm.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.topscomm.pub.util.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class OfficeOperateUtilExpand extends OfficeOperateUtil {

    public static InputStream exportExeclXlsxWithMergeHeader(List<Map<String, Object>> listMap, JSONArray columns) throws Exception {
        int rowIndex = 0;
        XSSFWorkbook wb = new XSSFWorkbook();
        // 表头格式
        XSSFCellStyle headCellStyle = wb.createCellStyle();
        headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        headCellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
        headCellStyle.setBorderLeft(BorderStyle.THIN);
        headCellStyle.setBorderRight(BorderStyle.THIN);
        headCellStyle.setBorderTop(BorderStyle.THIN);
        headCellStyle.setBorderBottom(BorderStyle.THIN);
        XSSFFont font = wb.createFont();
        font.setFontName(I18nUtil.getMessage("tap.common.songti"));
        font.setBold(true);
        headCellStyle.setFont(font);
        // 表行格式
        XSSFCellStyle lineCellStyle = wb.createCellStyle();
        lineCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        lineCellStyle.setAlignment(HorizontalAlignment.CENTER);
        lineCellStyle.setBorderLeft(BorderStyle.THIN);
        lineCellStyle.setBorderRight(BorderStyle.THIN);
        lineCellStyle.setBorderTop(BorderStyle.THIN);
        lineCellStyle.setBorderBottom(BorderStyle.THIN);
        lineCellStyle.setWrapText(true);
        // 表头
        //创建sheet
        XSSFSheet sheet = wb.createSheet("sheet1");
        //创建表行
        XSSFRow row = sheet.createRow(rowIndex++);
        //列index
        int colindex =  0;
        //遍历columns 取出一级表头
        for (int i = 0; i < columns.size(); i++) {
            JSONObject column = columns.getJSONObject(i);
//            Object obj = columns.getJSONObject(i).get("code");
//            JSONObject column = (JSONObject)JSONObject.toJSON(obj);
            XSSFCell cell = row.createCell(colindex);
            cell.setCellStyle(headCellStyle);
            //设置label为 中文名
            String colname = (String) column.get("label");
            cell.setCellValue(colname);
            //如果存在子表头
            if(column.getJSONArray("subcols").size()>0){
                //取得子表头
                JSONArray subcols = column.getJSONArray("subcols");
                int colindexEnd = colindex + subcols.size()-1;
                //排除单列父子表头 避免报错
                if(colindexEnd != colindex){
                    //合并单元格（起始行，结束行，起始列，结束列）
                    CellRangeAddress mergedHead = new CellRangeAddress(row.getRowNum(),row.getRowNum(),colindex,colindexEnd);
                    sheet.addMergedRegion(mergedHead);
                }
                colindex+=subcols.size();
            }else{
                //无子表头 合并两行
                CellRangeAddress mergedHead = new CellRangeAddress(row.getRowNum(),row.getRowNum()+1,colindex,colindex);
                sheet.addMergedRegion(mergedHead);
                colindex++;
            }
        }
        //表头第二行 将所有子表头展开
        row = sheet.createRow(rowIndex++);
        colindex = 0;
        for (int i = 0; i < columns.size(); i++) {
            JSONObject column = columns.getJSONObject(i);
            //如果有子表头
            if(column.getJSONArray("subcols").size()>0) {
                JSONArray subcols = column.getJSONArray("subcols");
                for (int j = 0; j <subcols.size() ; j++) {
                    XSSFCell cell = row.createCell(colindex);
                    cell.setCellStyle(headCellStyle);
                    String colname = (String) subcols.getJSONObject(j).get("label");
                    cell.setCellValue(colname);
                    colindex++;
                }
            }else {
                colindex++;
            }
        }
        //创建表头之后 压缩JSONArray结构 获得flatColumns 使 column 和 sql字段一一对应
        JSONArray flatColumns = new JSONArray();
        for (int i = 0; i <columns.size(); i++) {
//            int tail = i;
            JSONObject column = columns.getJSONObject(i);
            //如果有子表头
            if (column.getJSONArray("subcols").size() > 0) {
                JSONArray subcols = column.getJSONArray("subcols");
                //遍历取得子表头内容
                for (int j = 0; j < subcols.size(); j++) {
                    flatColumns.add(subcols.get(j));
                }
            }else {
                flatColumns.add(column);
            }
        }
        //表行
        //遍历ListMap
        for (Map<String, Object> data : listMap) {
            row = sheet.createRow(rowIndex++);
            for (int i = 0; i < flatColumns.size(); i++) {
                //获取prop
                String key = ConvertUtil.convertToString(flatColumns.getJSONObject(i).get("prop"));
                XSSFCell cell = row.createCell(i);
                String value = "";
                if("index".contentEquals(key)){
                    //存在序号 赋值序号
                    cell.setCellValue(row.getRowNum()-1);
                }else if(data.get(key)==null){
                    cell.setCellValue("");
                } else if (!StringUtil.isEmpty(key) ) {
                    if (data.get(key) instanceof Date) {
                        value = DateUtil.dateToString((Date) data.get(key));
                        cell.setCellValue(value);
                    } else if (data.get(key) instanceof BigDecimal || data.get(key) instanceof Double
                            || data.get(key) instanceof Float) {
                        double d = ConvertUtil.convertToDouble(data.get(key));
                        cell.setCellValue(d);
                    }
                    else if (data.get(key).toString().isEmpty()){
                        cell.setCellValue("");
                    }else {//展开二级表头
                        value = data.get(key).toString();
                        cell.setCellValue(value);
                    }
                }
                cell.setCellStyle(lineCellStyle);
            }
        }
        // 自适应列
        int columnCount = sheet.getRow(1).getLastCellNum();
        for (int i = 0; i < columnCount; i++) {
            sheet.autoSizeColumn(i);
            int colWidth = sheet.getColumnWidth(i) * 17 / 10;
            if (colWidth < 255 * 256) {
                sheet.setColumnWidth(i, colWidth);
            } else {
                sheet.setColumnWidth(i, 6000);
            }
        }
        // 生成流
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        InputStream is = null;
        try {
            wb.write(os);
            os.flush();
            byte[] content = os.toByteArray();
            is = new ByteArrayInputStream(content);
        } catch (Exception ex) {
            if (os != null) {
                os.close();
                os = null;
            }
            if (wb != null) {
                wb.close();
            }
        }
        return is;
    }
}
